﻿using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using 图书管理.Model;
using Dapper;

namespace 图书管理.Services
{
    internal class BookInfoUtils
    {
        private static readonly string constr = "Data Source=.;Initial Catalog=books;User Id=sa;Password=root123;Encrypt=False";

        internal IEnumerable<BookInfo> SelectAll()
        {
            using (var connection = new SqlConnection(constr))
            {
                var bookTypes = connection.Query<BookInfo>(" select id,name,writer,publisher,type,describe from bookInfo order by id asc ");
                return bookTypes;
            }
        }

        internal bool EditBookInfo(BookInfo bookInfo)
        {
            bool b = false;

            using (var connection = new SqlConnection(constr))
            {
                int v = connection.Execute("UPDATE bookInfo SET name = @name,writer=@writer,publisher=@publisher,type=@type,describe=@describe WHERE Id = @Id", bookInfo);
                b = v > 0;
            }
            return b;
        }

        internal bool DeleteBookInfo(int selId)
        {
            bool b = false;

            using (var connection = new SqlConnection(constr))
            {
                int v = connection.Execute("DELETE FROM bookInfo WHERE Id = @Id", new { Id = selId });
                b = v > 0;
            }
            return b;
        }

        internal bool InsertBookInfo(BookInfo bookInfo)
        {
            bool b = false;

            using (var connection = new SqlConnection(constr))
            {
                int v = connection.Execute("INSERT INTO bookInfo (name,writer,publisher,type,describe) VALUES (@name,@writer,@publisher,@type,@describe)", bookInfo);
                b = v > 0;
            }
            return b;

        }

        internal IEnumerable<BookInfo> search(BookInfo bookInfo)
        {
            using (var connection = new SqlConnection(constr))
            {
                var sql = " select id,name,writer,publisher,type,describe from bookInfo ";
                if(bookInfo != null)
                {
                    sql += " where 1=1 ";
                    if(bookInfo.Name != null)
                    {
                        sql += " and name = @name ";
                    }

                    if(bookInfo.Type != null)
                    {
                        sql += " and type = @type ";
                    }

                    if(bookInfo.Publisher != null)
                    {
                        sql += " and publisher = @publisher ";
                    }

                    if(bookInfo.Writer != null)
                    {
                        sql += " and writer = @writer ";
                    }
                }

                var bookInfos = connection.Query<BookInfo>(sql,bookInfo);
                return bookInfos;
            }
        }

        internal BookInfo GetById(int selId)
        {
            using (var connection = new SqlConnection(constr))
            {
                var bookInfo = connection.QuerySingle<BookInfo>(" select id,name,writer,publisher,type,describe from bookInfo where id = @id ",new {id = selId});
                return bookInfo;
            }
        }
    }
}
